﻿using System;
using System.Collections.Generic;
using System.Data.SQLite;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace program1
{
    public class StudentRepository
    {
        private string connectionString;

        public StudentRepository(string connectionString)
        {
            this.connectionString = connectionString;
        }

        public void AddStudent(Student student)
        {
            using (SQLiteConnection connection = new SQLiteConnection(connectionString))
            {
                connection.Open();
                using (SQLiteCommand command = new SQLiteCommand("INSERT INTO Students (ClassID, Name, Age, Gender, Address) VALUES (@ClassID, @Name, @Age, @Gender, @Address)", connection))
                {
                    command.Parameters.AddWithValue("@ClassID", student.ClassID);
                    command.Parameters.AddWithValue("@Name", student.Name);
                    command.Parameters.AddWithValue("@Age", student.Age);
                    command.Parameters.AddWithValue("@Gender", student.Gender);
                    command.Parameters.AddWithValue("@Address", student.Address);
                    command.ExecuteNonQuery();
                }
            }
        }

        public void InsertStudent(int classID, string name, int age, string gender, string address)
        {
            using (SQLiteConnection connection = new SQLiteConnection(connectionString))
            {
                connection.Open();
                using (SQLiteCommand command = new SQLiteCommand(connection))
                {
                    command.CommandText = "INSERT INTO Students (ClassID, Name, Age, Gender, Address) VALUES (@ClassID, @Name, @Age, @Gender, @Address);";
                    command.Parameters.AddWithValue("@ClassID", classID);
                    command.Parameters.AddWithValue("@Name", name);
                    command.Parameters.AddWithValue("@Age", age);
                    command.Parameters.AddWithValue("@Gender", gender);
                    command.Parameters.AddWithValue("@Address", address);
                    command.ExecuteNonQuery();
                }
            }
        }

        public void DeleteStudent(int studentID)
        {
            using (SQLiteConnection connection = new SQLiteConnection(connectionString))
            {
                connection.Open();
                using (SQLiteCommand command = new SQLiteCommand("DELETE FROM Students WHERE StudentID = @StudentID", connection))
                {
                    command.Parameters.AddWithValue("@StudentID", studentID);
                    command.ExecuteNonQuery();
                }
            }
        }


        public void UpdateStudent(int studentID, string name, int age, string gender, string address)
        {
            using (SQLiteConnection connection = new SQLiteConnection(connectionString))
            {
                connection.Open();
                using (SQLiteCommand command = new SQLiteCommand(connection))
                {
                    command.CommandText = "UPDATE Students SET Name = @Name, Age = @Age, Gender = @Gender, Address = @Address WHERE StudentID = @StudentID;";
                    command.Parameters.AddWithValue("@Name", name);
                    command.Parameters.AddWithValue("@Age", age);
                    command.Parameters.AddWithValue("@Gender", gender);
                    command.Parameters.AddWithValue("@Address", address);
                    command.Parameters.AddWithValue("@StudentID", studentID);
                    command.ExecuteNonQuery();
                }
            }
        }

        public List<Student> GetStudentsByClass(int classID)
        {
            List<Student> students = new List<Student>();

            using (SQLiteConnection connection = new SQLiteConnection(connectionString))
            {
                connection.Open();
                using (SQLiteCommand command = new SQLiteCommand("SELECT * FROM Students WHERE ClassID = @ClassID", connection))
                {
                    command.Parameters.AddWithValue("@ClassID", classID);
                    using (SQLiteDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            Student student = new Student
                            {
                                StudentID = Convert.ToInt32(reader["StudentID"]),
                                ClassID = Convert.ToInt32(reader["ClassID"]),
                                Name = reader["Name"].ToString(),
                                Age = Convert.ToInt32(reader["Age"]),
                                Gender = reader["Gender"].ToString(),
                                Address = reader["Address"].ToString()
                            };
                            students.Add(student);
                        }
                    }
                }
            }

            return students;
        }

        public List<Student> GetAllStudents()
        {
            List<Student> students = new List<Student>();

            using (SQLiteConnection connection = new SQLiteConnection(connectionString))
            {
                connection.Open();
                using (SQLiteCommand command = new SQLiteCommand("SELECT * FROM Students", connection))
                using (SQLiteDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        Student student = new Student
                        {
                            StudentID = Convert.ToInt32(reader["StudentID"]),
                            ClassID = Convert.ToInt32(reader["ClassID"]),
                            Name = reader["Name"].ToString(),
                            Age = Convert.ToInt32(reader["Age"]),
                            Gender = reader["Gender"].ToString(),
                            Address = reader["Address"].ToString()
                        };
                        students.Add(student);
                    }
                }
            }

            return students;
        }

        public List<Student> GetStudents(int studentID)
        {
            List<Student> students = new List<Student>();

            using (SQLiteConnection connection = new SQLiteConnection(connectionString))
            {
                connection.Open();
                using (SQLiteCommand command = new SQLiteCommand(connection))
                {
                    command.CommandText = "SELECT * FROM Students";
                    if (studentID > 0)
                    {
                        command.CommandText += " WHERE StudentID = @StudentID";
                        command.Parameters.AddWithValue("@StudentID", studentID);
                    }

                    using (SQLiteDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            Student student = new Student
                            {
                                StudentID = reader.GetInt32(reader.GetOrdinal("StudentID")),
                                ClassID = reader.GetInt32(reader.GetOrdinal("ClassID")),
                                Name = reader.GetString(reader.GetOrdinal("Name")),
                                Age = reader.GetInt32(reader.GetOrdinal("Age")),
                                Gender = reader.GetString(reader.GetOrdinal("Gender")),
                                Address = reader.GetString(reader.GetOrdinal("Address"))
                            };
                            students.Add(student);
                        }
                    }
                }
            }

            return students;
        }
    }
}
